package com.team.arms.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;


import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

@Component
public class ExcelUtil {
    /**
     * 根据文档格式返回相对应的对象
     *
     * @param file
     * @param pattern
     * @return
     */
    public static Workbook readExcel(MultipartFile file, String pattern) {
        Workbook workbook = null;
        if (file != null) {
            try {
                InputStream is = file.getInputStream();
                if (pattern.equals("xls")) {
                    return workbook = new HSSFWorkbook(is);
                }
                if (pattern.equals("xlsx")) {
                    return workbook = new XSSFWorkbook(is);
                } else {
                    return null;
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return workbook;
    }

    /**
     * 判断文件是否为Excel文件
     *
     * @param file
     * @return
     */
    public static boolean isExcel(MultipartFile file) {
        if (file != null) {
            String filename = file.getOriginalFilename();
            String substring = filename.substring(filename.lastIndexOf(".") + 1);
            substring = substring.toLowerCase();
            if (substring.equals("xls") || substring.equals("xlsx")) {
                return true;
            }
        }
        return false;
    }

    /**
     * 读取Excel
     * @param file
     * @param pattern
     * @return
     */
    public static List<List<String>> readExcelContents(MultipartFile file, String pattern) {
        List<List<String>> listRow = new ArrayList<>();
        //文档对象
        Workbook workbook = null;
        //表格对象
        Sheet sheet = null;
        //非空和文件格式判断
        if (isExcel(file)) {
            workbook = readExcel(file, pattern);
        }
        if (workbook != null) {
            //获取第一个表格
            sheet = workbook.getSheetAt(0);
            int rowNum = sheet.getPhysicalNumberOfRows();
            //行对象
//            Row row = null;

            Row row = sheet.getRow(0); //先获取导入的excel文件中第一行的对象
            int colnum = row.getPhysicalNumberOfCells();//获取第一行的最大列数
//            System.out.println("最大列数为"+colnum);
            //单元格数据
            String cellData = null;
            // i=1 直接跳过第一行
            for (int i = 1; i < rowNum; i++) {
                row = sheet.getRow(i);
                List<String> listCell = new ArrayList<>();
                if (StringUtils.isBlank(row.getCell(0).toString())) {
                        break;
                }
                //读取几列
                for (int j = 0; j < colnum; j++) {
                    cellData = String.valueOf(row.getCell(j));
//                    System.out.println(row.getCell(j));
                    listCell.add(cellData);
                }

                listRow.add(listCell);
            }
        }
        return listRow;
    }
}
